#!/usr/bin/python3
# -*- coding:utf-8 -*-
""""
   @Author: 叶建伟
   @Email: yejw46507@hundsun.com
   @Time: 2023/7/5 13:58
   @File: threadInsertBTA.py
"""
import threading
import queue
from queue import Empty
from DataTest.databases import MysqlUtil, DaMengUtil, OracleUtil
import datetime

# 定义全局变量num_threads线程数量，num_inserts插入数据总量，batch_size单次插入数量
from DataTest.tabledata.commonData.SQLMontage import get_sql_key, get_sql, montage_sql_value
from DataTest.tabledata.commonData.clientInfo import client_info_hsraw

insert_number = 50
num_threads = 1
sql_number = 1
processes = []

# 定义队列用于存储拼接好的SQL语句
sql_queue = queue.Queue()

# 定义锁，用于保证线程安全
lock = threading.Lock()

BLOCK = True
customerinfo_key = get_sql_key(table='taaml_n_customerinfo')
customerinfo_value = get_sql(table='taaml_n_customerinfo')
request_key = get_sql_key(table='taaml_n_request')
request_value = get_sql(table='taaml_n_request')
confirm_key = get_sql_key(table='taaml_n_confirm')
confirm_value = get_sql(table='taaml_n_confirm')
accorequest_key = get_sql_key(table='taaml_n_accorequest')
accorequest_value = get_sql(table='taaml_n_accorequest')
acconet_key = get_sql_key(table='taaml_n_acconet')
acconet_value = get_sql(table='taaml_n_acconet')
divide_key = get_sql_key(table='taaml_n_dividenddetail')
divide_value = get_sql(table='taaml_n_dividenddetail')
static_key = get_sql_key(table='taaml_n_staticshares')
static_value = get_sql(table='taaml_n_staticshares')

# 定义线程执行的函数：SQL拼接
def sql_assemble(thread_id):
    for i in range(insert_number//num_threads//sql_number):
        customerinfo_sql = customerinfo_key
        accorequest_sql = accorequest_key
        acconet_sql = acconet_key
        divide_sql = divide_key
        static_sql = static_key
        request_sql = request_key
        confirm_sql = confirm_key
        for i in range(0,sql_number):
            sql_list = client_info_hsraw()
            customerinfo_sql = customerinfo_sql+ montage_sql_value(table=customerinfo_value, sql_list=sql_list)
            accorequest_sql = accorequest_sql + montage_sql_value(table=accorequest_value, sql_list=sql_list)
            acconet_sql = acconet_sql + montage_sql_value(table=acconet_value, sql_list=sql_list)
            divide_sql = divide_sql + montage_sql_value(table=divide_value, sql_list=sql_list)
            static_sql = static_sql + montage_sql_value(table=static_value, sql_list=sql_list)
            for j in range(3):
                c_bankname = sql_list.get('c_bankname')
                c_bankno = sql_list.get('c_bankno')
                c_bankacco = sql_list.get('c_bankacco')
                c_fundacco = sql_list.get('c_fundacco')
                c_tradeacco = sql_list.get('c_tradeacco')
                c_agencyno = sql_list.get('c_agencyno')
                sql_list1 = client_info_hsraw()
                sql_list1.update(
                    {'c_bankname': c_bankname, 'c_bankno': c_bankno, 'c_bankacco': c_bankacco, 'c_fundacco': c_fundacco,
                     'c_tradeacco': c_tradeacco, 'c_agencyno': c_agencyno})
                if i == sql_number-1 and j == 2:
                    request_sql = request_sql + montage_sql_value(table=request_value, sql_list=sql_list1)+ ';'
                    confirm_sql = confirm_sql + montage_sql_value(table=confirm_value, sql_list=sql_list1)+ ';'
                    sql_queue.put(request_sql)
                    sql_queue.put(confirm_sql)
                    request_sql = request_key
                    confirm_sql = confirm_key
                else:
                    request_sql = request_sql + montage_sql_value(table=request_value, sql_list=sql_list1)+ ','
                    confirm_sql = confirm_sql + montage_sql_value(table=confirm_value, sql_list=sql_list1)+ ','


            if i < sql_number-1 :
                customerinfo_sql = customerinfo_sql + ','
                accorequest_sql = accorequest_sql + ','
                acconet_sql = acconet_sql + ','
                divide_sql = divide_sql + ','
                static_sql = static_sql + ','
            else:
                customerinfo_sql = customerinfo_sql + ';'
                accorequest_sql = accorequest_sql + ';'
                acconet_sql = acconet_sql + ';'
                divide_sql = divide_sql + ';'
                static_sql = static_sql + ';'
                sql_queue.put(customerinfo_sql)
                sql_queue.put(accorequest_sql)
                sql_queue.put(acconet_sql)
                sql_queue.put(divide_sql)
                sql_queue.put(static_sql)


        # print(customerinfo_sql)
        # print(accorequest_sql)
        # print(acconet_sql)
        # print(divide_sql)
        # print(static_sql)
        # print(request_sql)
        # print(confirm_sql)





# 定义线程执行的函数：SQL插入
def sql_insert(db):
    # while not sql_queue.empty():
    global sql
    while True:
        lock.acquire(blocking=True, timeout=-1)
        try:
            # print(sql_queue.qsize())
            sql = sql_queue.get(block=BLOCK)
            # print(f'插入sql:{sql}\n')
            # 执行SQL插入操作
        except Empty as e:
            break
        finally:
            lock.release()
            if sql:
                # print(sql)
                db.execsql(sql)
                print(datetime.datetime.now())

            else:
                break


if __name__ == '__main__':
    print(datetime.datetime.now())
    # 创建并启动SQL拼接线程
    assemble_threads = []
    for i in range(num_threads):
        thread = threading.Thread(target=sql_assemble, args=(i,))
        thread.start()
        assemble_threads.append(thread)

    # 创建并启动SQL插入线程
    insert_threads = []
    # db = MysqlUtil('root', 'fxhg@1234', '10.20.181.116', port=33061)
    # db = MysqlUtil('root', 'fxq5@123', '10.20.46.217', port=33061)
    db = OracleUtil('hsraw', 'fxq5@123', '10.20.37.111', server='bsaml')
    for i in range(num_threads):
        thread = threading.Thread(target=sql_insert, args=(db,))
        thread.start()
        insert_threads.append(thread)

    #等待所有SQL拼接线程执行完毕
    for thread in assemble_threads:
        thread.join()
    else:
        BLOCK = False
        sql_queue.put(False)

    # 等待所有SQL插入线程执行完毕
    # for thread in insert_threads:
    #     thread.join()
    db.commit()
    print(datetime.datetime.now())
